/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.tces.controller;

import com.tces.model.Students;
import com.tces.utils.ConnectionDAO;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class StudentDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(StudentDAO.class);

    /**
     * this method will check the validity of the student
     *
     * @param studentIDNumber - School ID number of the student
     * @param studentPassword - Password of the student
     * @return Student object that based on the @password and @studentIDNumber
     * @throws Exception
     */
    public static Students authenticate(String studentIDNumber, String studentPassword) throws Exception {
        Students student = null;
        String sql = "SELECT\n"
                + "s.student_id,\n"
                + "pr.program_revision_name,\n"
                + "p.program_code,\n"
                + "c.college_code,\n"
                + "c.college_id,\n"
                + "p.program_id,\n"
                + "pr.program_revision_id,\n"
                + "s.student_idno,\n"
                + "s.student_first_name,\n"
                + "s.student_middle_name,\n"
                + "s.student_last_name,\n"
                + "s.student_visible\n"
                + "FROM\n"
                + "students AS s\n"
                + "INNER JOIN program_revisions AS pr ON s.program_revision_id = pr.program_revision_id\n"
                + "INNER JOIN programs AS p ON pr.program_id = p.program_id\n"
                + "INNER JOIN colleges AS c ON p.college_id = c.college_id\n"
                + "WHERE s.student_password = MD5(?)\n"
                + "AND s.student_idno = ?";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        preparedStatement.setString(1, studentPassword);
        preparedStatement.setString(2, studentIDNumber);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        
        while (rs.next()) {
            student = new Students();
            student.setStudentID(rs.getInt("student_id"));
            student.setProgramID(rs.getInt("program_id"));
            student.setCollegeID(rs.getInt("college_id"));
            student.setProgramRevisionID(rs.getInt("program_revision_id"));
            student.setStudentVisible(rs.getInt("student_visible"));
            student.setCollegeCode(rs.getString("college_code"));
            student.setProgramRevisionName(rs.getString("program_revision_name"));
            student.setProgramCode(rs.getString("program_code"));
            student.setStudentIDNumber(rs.getString("student_idno"));
            student.setStudentFirstName(rs.getString("student_first_name"));
            student.setStudentMiddleName(rs.getString("student_middle_name"));
            student.setStudentLastName(rs.getString("student_last_name"));
        }
        closeConnection(getConnectionObject());

        return student;
    }

    /**
     * this method will return all the students regardless if its softly deleted
     * or not
     *
     * @return
     * @throws Exception
     */
    public static List<Students> findAll() throws Exception {
        List<Students> studentList = new ArrayList<Students>();
        String sql = "SELECT\n"
                + "s.student_id,\n"
                + "pr.program_revision_name,\n"
                + "p.program_code,\n"
                + "c.college_code,\n"
                + "c.college_id,\n"
                + "p.program_id,\n"
                + "pr.program_revision_id,\n"
                + "s.student_idno,\n"
                + "s.student_first_name,\n"
                + "s.student_middle_name,\n"
                + "s.student_last_name,\n"
                + "s.student_visible\n"
                + "FROM\n"
                + "students AS s\n"
                + "INNER JOIN program_revisions AS pr ON s.program_revision_id = pr.program_revision_id\n"
                + "INNER JOIN programs AS p ON pr.program_id = p.program_id\n"
                + "INNER JOIN colleges AS c ON p.college_id = c.college_id";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
//        preparedStatement.setInt(1, subjectOfferingID);
        log.info(preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        Students student;
        while (rs.next()) {
            student = new Students();
            student.setStudentID(rs.getInt("student_id"));
            student.setProgramID(rs.getInt("program_id"));
            student.setCollegeID(rs.getInt("college_id"));
            student.setProgramRevisionID(rs.getInt("program_revision_id"));
            student.setStudentVisible(rs.getInt("student_visible"));
            student.setCollegeCode(rs.getString("college_code"));
            student.setProgramRevisionName(rs.getString("program_revision_name"));
            student.setProgramCode(rs.getString("program_code"));
            student.setStudentIDNumber(rs.getString("student_idno"));
            student.setStudentFirstName(rs.getString("student_first_name"));
            student.setStudentMiddleName(rs.getString("student_middle_name"));
            student.setStudentLastName(rs.getString("student_last_name"));
            studentList.add(student);
        }
        closeConnection(getConnectionObject());

        return studentList;
    }
        public static int savePassword(String studentIDNumber, String studentPassword) throws Exception {
        int result = 0;
        try {
            String sql = "UPDATE students SET student_password = MD5(?) WHERE student_idno = ?";
            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setString(1, studentPassword);
            preparedStatement.setString(2, studentIDNumber);
            log.info(preparedStatement.toString());
            return preparedStatement.executeUpdate();
        } catch (Exception ex) {
            log.error(ex);
        }
        return result;
    }
}
